-- 创建团队角色枚举类型
CREATE TYPE team_role AS ENUM ('leader', 'member', 'pending');

-- 创建团队状态枚举类型
CREATE TYPE team_status AS ENUM ('active', 'disbanded', 'completed');

-- 创建团队表
CREATE TABLE IF NOT EXISTS teams (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    avatar_url VARCHAR(500),
    
    -- 关联竞赛
    competition_id BIGINT NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
    
    -- 团队设置
    is_public BOOLEAN DEFAULT true,
    max_members INTEGER DEFAULT 5,
    required_skills JSONB DEFAULT '[]'::jsonb,
    
    -- 状态
    status team_status DEFAULT 'active',
    member_count INTEGER DEFAULT 1,
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建团队成员表
CREATE TABLE IF NOT EXISTS team_members (
    id BIGSERIAL PRIMARY KEY,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role team_role DEFAULT 'member',
    
    -- 加入信息
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    contribution_note TEXT,
    
    -- 确保用户在同一团队中只能有一个角色
    UNIQUE(team_id, user_id)
);

-- 创建团队邀请表
CREATE TABLE IF NOT EXISTS team_invitations (
    id BIGSERIAL PRIMARY KEY,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    inviter_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    invitee_email VARCHAR(255) NOT NULL,
    invitee_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    
    message TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    responded_at TIMESTAMP WITH TIME ZONE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_teams_competition_id ON teams(competition_id);
CREATE INDEX IF NOT EXISTS idx_teams_status ON teams(status);
CREATE INDEX IF NOT EXISTS idx_teams_is_public ON teams(is_public);
CREATE INDEX IF NOT EXISTS idx_teams_created_at ON teams(created_at);

CREATE INDEX IF NOT EXISTS idx_team_members_team_id ON team_members(team_id);
CREATE INDEX IF NOT EXISTS idx_team_members_user_id ON team_members(user_id);
CREATE INDEX IF NOT EXISTS idx_team_members_role ON team_members(role);

CREATE INDEX IF NOT EXISTS idx_team_invitations_team_id ON team_invitations(team_id);
CREATE INDEX IF NOT EXISTS idx_team_invitations_invitee_email ON team_invitations(invitee_email);
CREATE INDEX IF NOT EXISTS idx_team_invitations_status ON team_invitations(status);
CREATE INDEX IF NOT EXISTS idx_team_invitations_expires_at ON team_invitations(expires_at);

-- 为团队表创建更新时间触发器
DROP TRIGGER IF EXISTS update_teams_updated_at ON teams;
CREATE TRIGGER update_teams_updated_at
    BEFORE UPDATE ON teams
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 创建更新团队成员数量的函数
CREATE OR REPLACE FUNCTION update_team_member_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE teams SET member_count = member_count + 1 WHERE id = NEW.team_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE teams SET member_count = member_count - 1 WHERE id = OLD.team_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ language 'plpgsql';

-- 创建团队成员数量更新触发器
DROP TRIGGER IF EXISTS update_team_member_count_trigger ON team_members;
CREATE TRIGGER update_team_member_count_trigger
    AFTER INSERT OR DELETE ON team_members
    FOR EACH ROW
    EXECUTE FUNCTION update_team_member_count();

-- 启用 RLS
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_invitations ENABLE ROW LEVEL SECURITY;

-- 团队表策略
DROP POLICY IF EXISTS "Anyone can view public teams" ON teams;
CREATE POLICY "Anyone can view public teams" ON teams
    FOR SELECT USING (is_public = true OR EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = teams.id 
        AND team_members.user_id = (auth.uid()::text)::bigint
    ));

DROP POLICY IF EXISTS "Team members can update team" ON teams;
CREATE POLICY "Team members can update team" ON teams
    FOR UPDATE USING (EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = teams.id 
        AND team_members.user_id = (auth.uid()::text)::bigint
        AND team_members.role IN ('leader', 'member')
    ));

DROP POLICY IF EXISTS "Authenticated users can create teams" ON teams;
CREATE POLICY "Authenticated users can create teams" ON teams
    FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- 团队成员表策略
DROP POLICY IF EXISTS "Anyone can view team members" ON team_members;
CREATE POLICY "Anyone can view team members" ON team_members
    FOR SELECT USING (true);

DROP POLICY IF EXISTS "Team leaders can manage members" ON team_members;
CREATE POLICY "Team leaders can manage members" ON team_members
    FOR ALL USING (EXISTS (
        SELECT 1 FROM team_members tm 
        WHERE tm.team_id = team_members.team_id 
        AND tm.user_id = (auth.uid()::text)::bigint
        AND tm.role = 'leader'
    ));

DROP POLICY IF EXISTS "Users can join teams" ON team_members;
CREATE POLICY "Users can join teams" ON team_members
    FOR INSERT WITH CHECK (user_id = (auth.uid()::text)::bigint);

-- 团队邀请表策略
DROP POLICY IF EXISTS "Team members can view invitations" ON team_invitations;
CREATE POLICY "Team members can view invitations" ON team_invitations
    FOR SELECT USING (EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = team_invitations.team_id 
        AND team_members.user_id = (auth.uid()::text)::bigint
    ) OR invitee_id = (auth.uid()::text)::bigint);

DROP POLICY IF EXISTS "Team members can create invitations" ON team_invitations;
CREATE POLICY "Team members can create invitations" ON team_invitations
    FOR INSERT WITH CHECK (EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = team_invitations.team_id 
        AND team_members.user_id = (auth.uid()::text)::bigint
        AND team_members.role IN ('leader', 'member')
    ));

DROP POLICY IF EXISTS "Invitees can update invitations" ON team_invitations;
CREATE POLICY "Invitees can update invitations" ON team_invitations
    FOR UPDATE USING (invitee_id = (auth.uid()::text)::bigint);

-- 授权
GRANT SELECT ON teams TO anon;
GRANT ALL PRIVILEGES ON teams TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE teams_id_seq TO authenticated;

GRANT SELECT ON team_members TO anon;
GRANT ALL PRIVILEGES ON team_members TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE team_members_id_seq TO authenticated;

GRANT SELECT ON team_invitations TO anon;
GRANT ALL PRIVILEGES ON team_invitations TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE team_invitations_id_seq TO authenticated;

-- 插入测试团队数据
INSERT INTO teams (name, description, competition_id, required_skills, max_members) VALUES
('AI创新先锋队', '专注于AI技术创新，寻找机器学习和深度学习专家', 1, '["Python", "TensorFlow", "PyTorch", "Machine Learning"]', 4),
('全栈开发团队', '构建完整的Web应用，需要前端和后端开发者', 2, '["React", "Node.js", "JavaScript", "MongoDB"]', 5),
('区块链探索者', '探索区块链技术的无限可能', 3, '["Solidity", "Web3", "Blockchain", "Smart Contracts"]', 3),
('移动应用梦之队', '打造下一代移动应用体验', 4, '["React Native", "Flutter", "iOS", "Android"]', 4),
('数据科学实验室', '用数据驱动决策，发现隐藏的洞察', 5, '["Python", "R", "Data Analysis", "Statistics"]', 6);

-- 插入测试团队成员数据
INSERT INTO team_members (team_id, user_id, role) VALUES
(1, 1, 'leader'),
(1, 2, 'member'),
(2, 1, 'member'),
(2, 3, 'leader'),
(3, 4, 'leader'),
(4, 5, 'leader'),
(5, 2, 'leader'),
(5, 4, 'member');

-- 插入测试邀请数据
INSERT INTO team_invitations (team_id, inviter_id, invitee_email, message, expires_at) VALUES
(1, 1, 'newuser@example.com', '我们需要一位前端开发专家加入我们的AI项目！', NOW() + INTERVAL '7 days'),
(2, 3, 'designer@example.com', '寻找UI/UX设计师，一起打造优秀的用户体验', NOW() + INTERVAL '5 days'),
(3, 4, 'blockchain@example.com', '区块链项目需要智能合约开发者', NOW() + INTERVAL '10 days');